-- @owner: @haomeng802
-- @date: 2023/1/3
-- @testpoint: 间隔分区表结合connect by子句

--step1:创建间隔分区表，插入数据  expect:建表成功，插入数据成功
drop table if exists t_connectby_005 cascade;
create table t_connectby_005(e_id int,e_name varchar(10),l_id int,time_id date)
partition by range (time_id)
interval('1 month')
( partition p1 values less than ('2022-06-30 00:00:00'),
  partition p2 values less than ('2022-07-31 00:00:00'),
  partition p3 values less than ('2022-08-31 00:00:00'),
  partition p4 values less than ('2022-09-30 00:00:00'),
  partition p5 values less than ('2022-10-31 00:00:00')
);

insert into t_connectby_005 values(3,'arise',1,'2022-05-30 00:00:00');
insert into t_connectby_005 values(2,'jack',2,'2022-07-15 00:00:00');
insert into t_connectby_005 values(5,'judy',2,'2022-08-30 00:00:00');
insert into t_connectby_005 values(6,'lion',3,'2022-10-10 00:00:00');
insert into t_connectby_005 values(4,'scott',2,'2022-09-30 00:00:00');
insert into t_connectby_005 values(7,'tiger',3,'2022-09-22 00:00:00');
insert into t_connectby_005 values(1,'king',1,'2022-10-30 00:00:00');

--step2:查询以l_id为1开始的节点的直属节点  expect:成功
select * from t_connectby_005 start with l_id = 1 connect by nocycle prior l_id = e_id;

--step3:结合层次递归查询函数查看递归层级  expect:成功
select sys_connect_by_path(e_name,'-') as path, *, level from t_connectby_005 start with l_id = 1 connect by nocycle prior l_id = e_id;

--step4:清理环境   expect:成功
drop table t_connectby_005 cascade;
